﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO

Public Class VeDao
    Dim provider As New DataProvider

    Public Function LayDanhSachVe() As DataTable
        Dim sql As String = "SELECT * FROM Ve"
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function TimVeTheoMaLichGan(ByVal MaLichGan As Integer) As DataTable
        Dim sql As String = "SELECT v.MaLichGan as MaLich, v.MaGhe, v.TinhTrang, lv.GiaVe, lv.MaLoaiVe FROM Ve v, LoaiVe lv WHERE v.MaLoaiVe = lv.MaLoaiVe and  v.MaLichGan = " & MaLichGan

        Return provider.ThucThiCauTruyVan(sql)
    End Function


    Public Function TimVe(ByVal ve As VeDto) As DataTable
        Dim sql As String = "SELECT * FROM Ve WHERE 1 "
        If ve.MaGhe <> 0 Then
            sql += " and MaGhe =" & ve.MaGhe
        End If
        If ve.MaLich <> 0 Then
            sql += " and MaLich = " & ve.MaLich
        End If

        sql += " and TinhTrang = " & ve.TinhTrang

        If ve.LoaiVe <> 0 Then
            sql += " and LoaiVe =" & ve.LoaiVe
        End If

        Return provider.ThucThiCauTruyVan(sql)

    End Function

    Public Function ThemVe(ByVal ve As VeDto) As Integer
        Dim sql As String = "INSERT  INTO VE(MaGhe, MaLichGan, TinhTrang, MaLoaiVe) VALUES (?, ?, ?, ?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaGhe", ve.MaGhe))
        dsParameter.Add(New OleDbParameter("@MaLich", ve.MaLich))
        dsParameter.Add(New OleDbParameter("@TinhTrang", ve.TinhTrang))
        dsParameter.Add(New OleDbParameter("@MaLoaiVe", ve.LoaiVe))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatVe(ByVal ve As VeDto) As Integer
        Dim sql As String = "UPDATE VE SET TinhTrang = ?, MaLoaiVe = ? WHERE MaGhe = ? and MaLichGan = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@TinhTrang", ve.TinhTrang))
        dsParameter.Add(New OleDbParameter("@MaLoaiVe", ve.LoaiVe))
        dsParameter.Add(New OleDbParameter("@MaGhe", ve.MaGhe))
        dsParameter.Add(New OleDbParameter("@MaLichGan", ve.MaLich))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function XoaVe(ByVal maCanXoa As Long) As Integer
        '.......
    End Function

End Class
